﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using DTOFrameworks;
using System.Xml;
using FooFun.DataElements.Dtos;

namespace AccessGrant
{
    public class DataService
    {
        DataSet ds;

        public static DataService GetData()
        {
            return new DataService();
        }

        private DataService()
        {
            ds = new DataSet();
        }

        public int GetForumParentId(int id)
        {
            DataTable dt = GetForumTable();

            foreach (DataRow row in dt.Rows)
            {
                if (row["ForumId"].Equals(id))
                {
                    return (int)row["ParentId"];
                }
            }

            return 0;
        }

        public IList<int> ListAccountId(int id)
        {
            DataTable table = FillTable("SELECT * FROM t_Groups");

            IList<int> list = new List<int>();

            list.Add(id);

            ListAccountParent(table, list, id);

            return list;
        }

        private void ListAccountParent
            (DataTable table, IList<int> list, int id)
        {
            foreach (DataRow row in table.Rows)
            {
                int childId = (int)row["ChildId"];
                int parentId = (int)row["ParentId"];

                if (childId == id
                    && !list.Contains(parentId))
                {
                    list.Add(parentId);

                    ListAccountParent(table, list, parentId);
                }
            }
        }

        DataTable dt_Forums = null;
        public DataTable GetForumTable()
        {
            if (dt_Forums == null)
            {
                dt_Forums = FillTable("SELECT * FROM t_Forums");
            }
            return dt_Forums;
        }

        public DataTable GetAccountTable()
        {
            return FillTable("SELECT * FROM t_Accounts");
        }

        const string NAME = "AccessProvider";
        public static DataTable FillTable(string sql)
        {

            string providerName
                = ConfigurationManager
                .ConnectionStrings[NAME].ProviderName;

            DbProviderFactory factory
                = DbProviderFactories.GetFactory(providerName);
            DbDataAdapter da = factory.CreateDataAdapter();
            da.SelectCommand =
                factory.CreateCommand();
            da.SelectCommand.CommandText
                = sql;
            da.SelectCommand.Connection
                = factory.CreateConnection();
            da.SelectCommand.Connection.ConnectionString
                = ConfigurationManager
                .ConnectionStrings[NAME].ConnectionString;
            //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\FooFun\DataBase\FAQ.mdb";

            DataTable dt = new DataTable();

            da.Fill(dt);

            return dt;
        }

        public static int Save(Topic topic)
        {
            string providerName
                = ConfigurationManager
                .ConnectionStrings[NAME].ProviderName;

            XmlElementDataElement de
                = topic.DataElement as XmlElementDataElement;

            XmlWriterSettings settings = new XmlWriterSettings();
            settings.Indent = true;
            settings.Encoding = Encoding.UTF8;

            StringBuilder sb = new StringBuilder();
            XmlWriter xmlWriter = XmlWriter.Create(sb);

            de.Document.Save(xmlWriter);

            xmlWriter.Close();

            string content = sb.ToString();

            DbProviderFactory factory
               = DbProviderFactories.GetFactory(providerName);
            DbParameter parameter1 = factory.CreateParameter();
            parameter1.Value
                = topic.DataElement["Title/Content"].Value.ToString();

            DbParameter parameter2 = factory.CreateParameter();
            parameter2.Value = content;

            DbParameter parameter3 = factory.CreateParameter();
            parameter3.Value = topic.ForumId;


            using (DbConnection connection = SqlHelper.Connection)
            {
                connection.Open();
                DbCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"INSERT INTO t_Topics(Title,Content,ForumId)
            VALUES(?,?,?)";
                cmd.Parameters.Add(parameter1);
                cmd.Parameters.Add(parameter2);
                cmd.Parameters.Add(parameter3);

                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@IDENTITY";
                int id = (int)cmd.ExecuteScalar();

                connection.Close();

                return id;
            }
        }

        public static void Update(Topic topic)
        {
            string providerName
                = ConfigurationManager
                .ConnectionStrings[NAME].ProviderName;

            XmlElementDataElement de
                = topic.DataElement as XmlElementDataElement;

            XmlWriterSettings settings = new XmlWriterSettings();
            settings.Indent = true;

            StringBuilder sb = new StringBuilder();
            XmlWriter xmlWriter = XmlWriter.Create(sb);

            de.Document.Save(xmlWriter);

            xmlWriter.Close();

            string content = sb.ToString();

            DbProviderFactory factory
               = DbProviderFactories.GetFactory(providerName);
            DbParameter parameter1 = factory.CreateParameter();
            parameter1.Value = content;

            DbParameter parameter2 = factory.CreateParameter();
            parameter2.Value = topic.TopicId;

            SqlHelper.ExecuteNonQuery(
                @"UPDATE t_Topics SET Content = ?
WHERE TopicId = ?", parameter1, parameter2);

        }

        public static Event Save(Event e)
        {
            e.CreateTime = DateTime.Now;

            using (DbConnection conn = SqlHelper.Connection)
            {
                string providerName
                = ConfigurationManager
                .ConnectionStrings[NAME].ProviderName;

                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText =
                    @"INSERT INTO t_Events
      (UserId, [CreateTime], IP, [Action], Description)
VALUES (?,?,?,?,?);";

                DbProviderFactory factory
              = DbProviderFactories.GetFactory(providerName);
                DbParameter parameter1 = factory.CreateParameter();
                parameter1.Value = e.UserId;

                DbParameter parameter2 = factory.CreateParameter();
                parameter2.Value = e.CreateTime.ToString();

                DbParameter parameter3 = factory.CreateParameter();
                parameter3.Value = e.Action;

                DbParameter parameter4 = factory.CreateParameter();
                parameter4.Value = e.Action;

                DbParameter parameter5 = factory.CreateParameter();
                parameter5.Value = e.Description;

                cmd.Parameters.AddRange(
                new DbParameter[] 
                { 
                    parameter1, parameter2, parameter3, 
                    parameter4, parameter5 
                }
                );
                conn.Open();

                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@IDENTITY;";

                int eventId = (int)cmd.ExecuteScalar();

                conn.Close();

                // todo: ..
                e.EventId = Guid.NewGuid();

                return e;
            }
        }
    }
}
